package com.mes.handler.plan;

import com.basis.utils.CodeGenerateRuleUtil;
import com.basis.utils.HibernateDao;
import com.mes.data.EmesColor;
import com.mes.data.EmesSize;
import com.mes.manufacture.data.EmesoManuOrder;
import com.wms.entity.WmsMaterialList;
import com.wms.entity.WmsMaterialListMx;
import org.codehaus.jettison.json.JSONObject;
import org.openbravo.client.kernel.BaseActionHandler;
import org.openbravo.dal.core.OBContext;
import org.openbravo.dal.service.OBDal;
import org.openbravo.model.common.enterprise.Warehouse;
import org.openbravo.model.common.plm.Product;
import org.openbravo.model.common.plm.ProductCategory;
import org.openbravo.model.common.uom.UOM;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 拉动计划生成领料单
 */
public class GenerateMaterialHandler extends BaseActionHandler {

	public static final String RAWMATERIAL = "RAWMATERIAL";
	private static final Map<String, ProductCategory> m_map = new HashMap();

	@Override
	protected JSONObject execute(Map<String, Object> parameters, String content) {
		JSONObject jsonRep = new JSONObject();
		String msg = "";
		try {
			//设置最高权限
			OBContext.setAdminMode();
			JSONObject reqData = new JSONObject(content);

			String[] ids = reqData.getString("ids").split(",");

			generateMaterialList(ids);
			UpdateManuOrderStatus(ids);
			jsonRep.put("result", true);
			jsonRep.put("msg", "处理成功！");

			if ("".equals(msg)) {
				jsonRep.put("flag", true);
				jsonRep.put("message", "处理成功");
				OBDal.getInstance().commitAndClose();
				return jsonRep;
			} else {
				jsonRep.put("flag", false);
				jsonRep.put("message", msg);
				OBDal.getInstance().rollbackAndClose();
				return jsonRep;
			}
		} catch (Exception e) {
			OBDal.getInstance().rollbackAndClose();
			e.printStackTrace();
			try {
				jsonRep.put("flag", false);
				jsonRep.put("message", e.getMessage());
				return jsonRep;
			} catch (Exception e2) {
			}
		} finally {
			OBContext.restorePreviousMode();
		}
		return null;
	}

	/**
	 * 修改拉动计划状态
	 * @param ids
	 */
	private void UpdateManuOrderStatus(String []ids) {
		for (String id : ids) {
			EmesoManuOrder emo = OBDal.getInstance().get(EmesoManuOrder.class, id);
			emo.setGenerateStatus("EXECUTED");
			OBDal.getInstance().save(emo);
		}
	}

	/**
	 * 生成领料单
	 * @param ids
	 */
	private void generateMaterialList(String[] ids) {
		List<Map<String, Object>> result = this.getPlanOrderBomList(ids);
		List<Map<String, Object>> warehouse = this.getWarehouseBomList(ids);

		WmsMaterialList material = null;
		WmsMaterialListMx materialMx = null;

		//生成非纱线类订单
		for (Map<String, Object> List : warehouse) {
			material = new WmsMaterialList();
			String code = CodeGenerateRuleUtil.getCode("Wms_Material_List", "code");

			material.setCode(code);
			material.setAimsWarehouse(OBDal.getInstance().get(Warehouse.class, List.get("m_warehouse_id")));
			material.setStatus("CREATE");

			OBDal.getInstance().save(material);

			for (Map<String, Object> map : result) {
				materialMx = new WmsMaterialListMx();

				//判断查出的明细是否属于该仓库
				if(List.get("m_warehouse_id").toString().equals(map.get("m_warehouse_id").toString())){

					String nums = "0";
					//根据物料编码、尺码、颜色查询该物料原材料库的库存
					String oql = "    select sum(kc.qty) as KCQTYS  " +
							"  from wms_storage_batch pc, wms_storage_in_out kc  " +
							"  where pc.wms_storage_batch_id = kc.wms_storage_batch_id  " +
							"  and kc.m_warehouse_id ='1E0A69B1B7BB4EECB232D6989684C4D7'  " ;
					if(map.get("m_product_id") != null && !map.get("m_product_id").equals("") &&  !map.get("m_product_id").equals("null")){
						oql += 	"  and pc.m_product_id = '"+map.get("m_product_id")+"'  " ;
					}
					if(map.get("emes_color_id") != null && !map.get("emes_color_id").equals("") &&  !map.get("emes_color_id").equals("null")){
						oql += 	"  and pc.emes_color_id =  '"+map.get("emes_color_id")+"'  " ;
					}

					if(map.get("emes_size_id") != null && !map.get("emes_size_id").equals("") &&  !map.get("emes_size_id").equals("null")){
						oql += 	"  and pc.emes_size_id = '"+map.get("emes_size_id")+"'  " ;
					}

					List<Map<String, Object>> qtyresults = HibernateDao.query(oql);

					for (Map<String, Object> qtymaps : qtyresults) {
						try {
							nums = qtymaps.get("KCQTYS").toString();
						} catch (Exception e) {
						}
					}
					BigDecimal numbers = new BigDecimal(nums); //该物料库存数
					BigDecimal qtys = new BigDecimal(map.get("SUMQTYS").toString()); //备料单数量

					materialMx.setWmsMaterialList(material);

					materialMx.setProduct(OBDal.getInstance().get(Product.class, map.get("m_product_id")));

					materialMx.setQty(qtys);

					materialMx.setQtyStock(numbers);

					materialMx.setUom(OBDal.getInstance().get(UOM.class, map.get("c_uom_id")));

					if(map.get("emes_color_id") != null && !map.get("emes_color_id").equals("") &&  !map.get("emes_color_id").equals("null")){
						materialMx.setEmesColor(OBDal.getInstance().get(EmesColor.class, map.get("emes_color_id")));
					}

					if(map.get("emes_size_id") != null && !map.get("emes_size_id").equals("") &&  !map.get("emes_size_id").equals("null")){
						materialMx.setEmesSize(OBDal.getInstance().get(EmesSize.class, map.get("emes_size_id")));
					}

					materialMx.setUseStatus("CREATE");

					OBDal.getInstance().save(materialMx);
				}
			}
		}

		/*//生成纱线类订单
		for (Map<String, Object> eq : equipments) {
			material = new WmsMaterialList();
			String code = CodeGenerateRuleUtil.getCode("Wms_Material_List","code");

			material.setCode(code);
			material.setAimsWarehouse(OBDal.getInstance().get(Warehouse.class, eq.get("m_warehouse_id")));
			material.setStatus("CREATE");

			OBDal.getInstance().save(material);

			for (Map<String, Object> sxList : sxLists) {
				materialMx = new WmsMaterialListMx();

				//判断查出的明细是否属于该仓库
				if(eq.get("m_warehouse_id").toString().equals(sxList.get("m_warehouse_id").toString())){

					String nums = "0";
					//根据物料编码、尺码、颜色查询该物料原材料库的库存
					String oql = "";
					oql += "    select sum(kc.qty) as KCQTYS  " +
							"  from wms_storage_batch pc, wms_storage_in_out kc  " +
							"  where pc.wms_storage_batch_id = kc.wms_storage_batch_id  " +
							"  and kc.m_warehouse_id ='1E0A69B1B7BB4EECB232D6989684C4D7'  " ;
					if(sxList.get("m_product_id") != null && !sxList.get("m_product_id").equals("") &&  !sxList.get("m_product_id").equals("null")){
						oql += 	"  and pc.m_product_id = '"+sxList.get("m_product_id")+"'  " ;
					}
					if(sxList.get("emes_color_id") != null && !sxList.get("emes_color_id").equals("") &&  !sxList.get("emes_color_id").equals("null")){
						oql += 	"  and pc.emes_color_id =  '"+sxList.get("emes_color_id")+"'  " ;
					}

					if(sxList.get("emes_size_id") != null && !sxList.get("emes_size_id").equals("") &&  !sxList.get("emes_size_id").equals("null")){
						oql += 	"  and pc.emes_size_id = '"+sxList.get("emes_size_id")+"'  " ;
					}


					List<Map<String, Object>> qtyresults = HibernateDao.query(oql);

					for (Map<String, Object> qtymaps : qtyresults) {
						try {
							nums = qtymaps.get("KCQTYS").toString();
						} catch (Exception e) {
						}
					}
					BigDecimal numbers = new BigDecimal(nums);



					materialMx.setWmsMaterialList(material);

					materialMx.setProduct(OBDal.getInstance().get(Product.class, sxList.get("m_product_id")));

					materialMx.setQty(new BigDecimal(sxList.get("SUMQTYS").toString()));

					materialMx.setQtyStock(numbers);

					materialMx.setUom(OBDal.getInstance().get(UOM.class, sxList.get("c_uom_id")));

					if(sxList.get("emes_color_id") != null && !sxList.get("emes_color_id").equals("") &&  !sxList.get("emes_color_id").equals("null")){
						materialMx.setEmesColor(OBDal.getInstance().get(EmesColor.class, sxList.get("emes_color_id")));
					}

					if(sxList.get("emes_size_id") != null && !sxList.get("emes_size_id").equals("") &&  !sxList.get("emes_size_id").equals("null")){
						materialMx.setEmesSize(OBDal.getInstance().get(EmesSize.class, sxList.get("emes_size_id")));
					}

					materialMx.setUseStatus("CREATE");

					OBDal.getInstance().save(materialMx);
				}
			}
		}*/

	}

	/**
	 * 根据传过来的拉动计划ID查出BOM明细(不包含纱线)
	 * @param ids
	 * @return
	 */
	private List<Map<String, Object>> getPlanOrderBomList(String []ids) {
		String sql = "";
		sql +="SELECT   " +
				" boms.m_product_id,   " +
				" wl.value,   " +
				" wl.name as WLNAME,    " +
				" fl.value as FLVALUE,   " +
				" SUM( boms.qty * sc.qty ) AS SUMQTYS,   " +
				" boms.c_uom_id,   " +
				" boms.emes_color_id,   " +
				" boms.emes_size_id,   " +
				" ck.m_warehouse_id    " +
				"FROM   " +
				" emeso_manuorder sc,   " +
				" emeso_manuorder_bom boms,   " +
				" wms_warehouse_product ck ,   " +
				" m_product wl ,   " +
				" M_Product_Category fl   " +
				"WHERE   " +
				" sc.emeso_manuorder_id = boms.emeso_manuorder_id    " +
				" AND boms.m_product_id = ck.m_product_id    " +
				" and boms.m_product_id = wl.m_product_id   " +
				" and wl.m_product_category_id = fl.m_product_category_id   " +
				"	and nvl(sc.generate_status, 'N') <> 'EXECUTED'"+
				" and fl.VALUE != 'SC-SX'" +
				"  and sc.emeso_manuorder_id in (''";

		for (String id : ids) {
			sql += ", ?";
		}
		sql += ") " +
				" GROUP BY " +
				" boms.m_product_id, " +
				" boms.c_uom_id, " +
				" boms.emes_color_id, " +
				" boms.emes_size_id, " +
				" ck.m_warehouse_id, " +
				" wl.value, wl.name,fl.value  ";
		return HibernateDao.query(sql, ids);
	}


	/**
	 * 查询BOM明细分属多少个仓库，生成多少个领料单
	 * @param ids
	 * @return
	 */
	private List<Map<String, Object>> getWarehouseBomList(String []ids) {
		String aql = "SELECT distinct ck.m_warehouse_id FROM " +
				"		emeso_manuorder sc, emeso_manuorder_bom boms, wms_warehouse_product ck, m_product wl, M_Product_Category fl  " +
				" WHERE sc.emeso_manuorder_id = boms.emeso_manuorder_id  " +
				"	AND boms.m_product_id = ck.m_product_id  " +
				"	AND boms.m_product_id = wl.m_product_id  " +
				"	AND wl.m_product_category_id = fl.m_product_category_id  " +
				"	AND fl.VALUE != 'SC-SX'" +
				"	and nvl(sc.generate_status, 'N') <> 'EXECUTED'"+
				"	and sc.emeso_manuorder_id in (''";
		for (String id : ids) {
			aql += ", ?";
		}
		aql += ")";
		return HibernateDao.query(aql, ids);
	}


	/**
	 * 根据传过来的拉动计划ID查出BOM明细(纱线处理)
	 * @param ids
	 * @return
	 */
	private List<Map<String, Object>> getPlanSxBomList(String []ids) {
		String sql = "";
		sql +="SELECT  " +
				" boms.m_product_id, " +
				" wl.value, " +
				" wl.name as WLNAME,  " +
				" fl.value as FLVALUE, " +
				" SUM( boms.qty * zy.qty ) AS SUMQTYS, " +
				" zygx.eam_facilities_id, " +
				" ck.m_warehouse_id , " +
				" boms.c_uom_id, " +
				" boms.emes_color_id, " +
				" boms.emes_size_id " +
				"FROM " +
				" emeso_manuorder sc, " +
				" emeso_manuorder_bom boms, " +
				" m_product wl , " +
				" M_Product_Category fl, " +
				" emeso_job_order zy, " +
				" emeso_job_order_exe zygx, " +
				" emes_process gx , " +
				" m_warehouse ck " +
				"WHERE " +
				" sc.emeso_manuorder_id = boms.emeso_manuorder_id  " +
				" and boms.m_product_id = wl.m_product_id " +
				" and wl.m_product_category_id = fl.m_product_category_id " +
				" and sc.emeso_manuorder_id = zy.emeso_manuorder_id " +
				" and zy.emeso_job_order_id = zygx.emeso_job_order_id " +
				" and zygx.emes_process_id = gx.emes_process_id " +
				" and zygx.eam_facilities_id = ck.eam_facilities_id " +
				" and fl.VALUE = 'SC-SX' " +
				" and gx.name = '飞织'" +
				"  and sc.emeso_manuorder_id in (''";

		for (String id : ids) {
			sql += ", ?";
		}
		sql += ") " +
				" GROUP BY " +
				" boms.m_product_id, " +
				" boms.c_uom_id, " +
				" boms.emes_color_id, " +
				" boms.emes_size_id, " +
				"zygx.eam_facilities_id, wl.value, wl.name,fl.value,ck.m_warehouse_id ";
		return HibernateDao.query(sql, ids);
	}

	/**
	 * 查询派工后生成的飞织机台明细
	 * @param ids
	 * @return
	 */
	private List<Map<String, Object>> getEquipmentList(String []ids) {

		String aql = " SELECT " +
				" zygx.eam_facilities_id AS EQID ," +
				" ck.m_warehouse_id" +
				" FROM " +
				" emeso_job_order zy, " +
				" emeso_job_order_exe zygx , " +
				" emes_process gx  ," +
				" m_warehouse ck" +
				" WHERE " +
				" zy.emeso_job_order_id = zygx.emeso_job_order_id  " +
				" and zygx.emes_process_id = gx.emes_process_id " +
				" and ck.eam_facilities_id = zygx.eam_facilities_id  "+
				"  and zy.emeso_manuorder_id in ('' " ;

		for (String id : ids) {
			aql += ", ?";
		}
		aql += ") " +
				" and gx.name = '飞织' " +
				" group by zygx.eam_facilities_id ,ck.m_warehouse_id ";
		return HibernateDao.query(aql, ids);
	}

}
